package com.F01.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;

import com.F01.actionForm.F010102ActionForm;
import com.F01.actionForm.F010104ActionForm;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;

/**
 * @wangk071226
 * @发货信息管理
 * @数据库操作类
 * @方法命名规则：静态参数名称、例如：查询方法，方法名 QUERYLIST();
 * @数据库某字段如果的新增值，如果要取当前时间，请在数据库中表时间的字段上设置GETDATE(), @ insert语句执行时，会自动插入时间。避免系统时间于服务器时间不统一造成错误。 @ 例表c_customers的customer_date字段。
 * 
 */
public class F010104DAO extends DbOperationExt {
	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F010104DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F010104DAO() {
		super();
	}

	public F010104DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @查询
	 */
	public List QUERYLIST(F010104ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		/** 判断查询条件，拼装查询SQL */
		/** 订单号码 */
		if (actionForm.getCotract_number() != null
				&& !actionForm.getCotract_number().equals("")) {
			condition.append(" and b.cotract_number = '"
					+ actionForm.getCotract_number().trim() + "' ");
		}
		/** 工 作 号 */
		if (actionForm.getManufacturing_order() != null
				&& !actionForm.getManufacturing_order().equals("")) {
			condition.append(" and a.manufacturing_order = '"
					+ actionForm.getManufacturing_order().trim() + "' ");
		}
		/** 入库时间 */
		if ((actionForm.getStock_date_begin() != null && !"".equals(actionForm
				.getStock_date_begin()))
				|| (actionForm.getStock_date_end() != null && !""
						.equals(actionForm.getStock_date_end()))) {

			if (!"".equals(actionForm.getStock_date_begin())
					&& !"".equals(actionForm.getStock_date_end())) {
				condition.append(" and stock_date BETWEEN '"
						+ actionForm.getStock_date_begin().trim() + "'");
				condition.append(" and '"
						+ actionForm.getStock_date_end().trim() + "' ");
			}
			if (!"".equals(actionForm.getStock_date_begin())
					&& "".equals(actionForm.getStock_date_end())) {
				condition.append(" and stock_date >='"
						+ actionForm.getStock_date_begin().trim() + "'");
			}
			if ("".equals(actionForm.getStock_date_begin())
					&& !"".equals(actionForm.getStock_date_end())) {
				condition.append(" and stock_date >='"
						+ actionForm.getStock_date_end() + "'");
			}
		}
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;

		msql.append("SELECT ");
		msql.append("  b.cotract_number as cotract_number,");
		msql.append("  a.manufacturing_order as manufacturing_order,");
		msql.append("  adyh.FUN_C_CUSTOMERS(customer_code) as customers_name,");
		msql.append("  b.customer_code as customer_code,");
		msql.append("  a.groups as groups,");
		msql.append("  a.qty as qty,");
		msql.append("  a.stock_date as stock_date,");
		msql.append("  CAST(ISNULL((a.qty-isnull((select isnull((sum(convert(float,isnull(c.send_amount,0)))),0) ");
		msql.append("  from pd_consignment c where a.manufacturing_order =  ");
		msql.append("  c.manufacturing_order and a.groups = c.groups group by c.manufacturing_order),0)),0) as varchar(20)) as stockpile  ");
		msql.append("FROM ");
		msql.append("  pd_finish_product a ");
		msql.append(" left join c_orders b on a.manufacturing_order=b.manufacturing_order ");
		msql.append(" where 1=1 " + condition.toString() + " ");
		msql.append(" order by  manufacturing_order ");
		try {
			log.info("发货信息管理 QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("cotract_number", CommonTools.null2String(rest
						.getString("cotract_number")));
				map.put("manufacturing_order", CommonTools.null2String(rest
						.getString("manufacturing_order")));
				map.put("customer_code", CommonTools.null2String(rest
						.getString("customer_code")));
				map.put("customers_name", CommonTools.null2String(rest
						.getString("customers_name")));
				map.put("groups", CommonTools.null2String(rest
						.getString("groups")));
				map.put("qty", CommonTools.null2String(rest.getString("qty")));
				map.put("stock_date", CommonTools.getDateTime(rest
						.getDate("stock_date")));
				map.put("stockpile", CommonTools.null2String(CommonTools
						.null2String(rest.getString("stockpile"))));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @新增
	 */
	public void ADD(F010104ActionForm actionForm, HttpServletRequest request) {
		ResultSet rest = null;
		StringBuffer msql = new StringBuffer();
		String groups = request.getParameter("groups");
		String stockpile = request.getParameter("stockpile");
		msql
				.append(" SELECT                                                     ");
		msql
				.append("  a.cotract_number as cotract_number,                       ");
		msql
				.append("  a.signaure_date as signaure_date,                         ");
		msql
				.append("  a.manufacturing_order as manufacturing_order,             ");
		msql
				.append("  a.model as model,                                         ");
		msql
				.append("  a.description as description,                             ");
		msql
				.append("  a.quantity as quantity,                                   ");
		msql
				.append("  a.VAT as VAT,                                             ");
		msql
				.append("  a.unit_price as unit_price,                               ");
		msql
				.append("  a.total_price as total_price,                             ");
		msql
				.append("  a.transport as transport,                                 ");
		msql
				.append("  a.commission as commission,                               ");
		msql
				.append("  a.customer_delivery_date as customer_delivery_date,       ");
		msql
				.append("  a.payment_terms as payment_terms,                         ");
		msql
				.append("  a.bank_guaranty as bank_guaranty,                         ");
		msql
				.append("  a.bond_date as bond_date,                                 ");
		msql
				.append("  a.bond_value as bond_value,                               ");
		msql
				.append("  a.customer_code as customer_code,                         ");
		msql
				.append("  a.area as area,                                           ");
		msql
				.append("  a.saleman_code as saleman_code,                           ");
		msql
				.append("  a.passing_date as passing_date,                           ");
		msql
				.append("  a.area_group as area_group,                               ");
		msql
				.append("  a.own_production as own_production,                       ");
		msql
				.append("  a.remark as orders_remark,                                ");
		msql
				.append("  a.customer_type as customer_type,                         ");
		msql
				.append("  a.product_type as product_type,                           ");
		msql
				.append("  b.customers_code as customers_code,                       ");
		msql
				.append("  b.customers_name as customers_name,                       ");
		msql
				.append("  b.address as address,                                     ");
		msql
				.append("  b.telephone_number as telephone_number,                   ");
		msql
				.append("  b.fax_number as fax_number,                               ");
		msql
				.append("  b.email_adress as email_adress,                           ");
		msql
				.append("  b.contact_name as contact_name,                           ");
		msql
				.append("  b.remark as customers_remark,                             ");
		msql
				.append("  b.customer_date as customer_date                          ");
		msql
				.append(" FROM c_orders a                                            ");
		msql
				.append(" left join c_customers b on a.customer_code=b.customers_code");
		msql
				.append(" where  manufacturing_order='");
		msql.append(CommonTools.null2String(request.getParameter(
				"manufacturing_order").trim())
				+ "'");

		try {
			log.info("发货信息管理 QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				/**订单信息*/
				actionForm.setCotract_number(CommonTools.null2String(rest
						.getString("cotract_number")));
				actionForm.setSignaure_date(CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("signaure_date")))));
				actionForm.setManufacturing_order(CommonTools.null2String(rest
						.getString("manufacturing_order")));
				actionForm.setOriginally_manufacturing_order(CommonTools
						.null2String(rest.getString("manufacturing_order")));
				actionForm.setOriginally_manufacturing_order(CommonTools
						.null2String(rest.getString("manufacturing_order")));
				actionForm.setModel(CommonTools.null2String(rest
						.getString("model")));
				actionForm.setDescription(CommonTools.null2String(rest
						.getString("description")));
				actionForm.setQuantity(CommonTools.null2String(rest
						.getString("quantity")));
				actionForm.setVAT(CommonTools
						.null2String(rest.getString("VAT")));
				actionForm.setUnit_price(CommonTools.null2String(rest
						.getString("unit_price")));
				actionForm.setTotal_price(CommonTools.null2String(rest
						.getString("total_price")));
				actionForm.setTransport(CommonTools.null2String(rest
						.getString("transport")));
				actionForm.setCommission(CommonTools.null2String(rest
						.getString("commission")));
				actionForm.setCustomer_delivery_date(CommonTools
						.null2String(CommonTools.getDateTime((rest
								.getDate("customer_delivery_date")))));
				actionForm.setPayment_terms(CommonTools.null2String(rest
						.getString("payment_terms")));
				actionForm.setBank_guaranty(CommonTools.null2String(rest
						.getString("bank_guaranty")));
				actionForm.setBond_date(CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("bond_date")))));
				actionForm.setBond_value(CommonTools.null2String(rest
						.getString("bond_value")));
				actionForm.setCustomer_code(CommonTools.null2String(rest
						.getString("customer_code")));
				actionForm.setArea(CommonTools.null2String(rest
						.getString("area")));
				actionForm.setSaleman_code(CommonTools.null2String(rest
						.getString("saleman_code")));
				actionForm.setPassing_date(CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("passing_date")))));
				actionForm.setArea_group(CommonTools.null2String(rest
						.getString("area_group")));
				actionForm.setOwn_production(CommonTools.null2String(rest
						.getString("own_production")));
				actionForm.setCustomer_type(CommonTools.null2String(rest
						.getString("customer_type")));
				actionForm.setProduct_type(CommonTools.null2String(rest
						.getString("product_type")));
				actionForm.setOrders_remark(CommonTools.null2String(rest
						.getString("orders_remark")));
				/**客户信息*/
				actionForm.setCustomers_code(CommonTools.null2String(rest
						.getString("customers_code")));
				actionForm.setCustomers_name(CommonTools.null2String(rest
						.getString("customers_name")));
				actionForm.setAddress(CommonTools.null2String(rest
						.getString("address")));
				actionForm.setTelephone_number(CommonTools.null2String(rest
						.getString("telephone_number")));
				actionForm.setFax_number(CommonTools.null2String(rest
						.getString("fax_number")));
				actionForm.setEmail_adress(CommonTools.null2String(rest
						.getString("email_adress")));
				actionForm.setContact_name(CommonTools.null2String(rest
						.getString("contact_name")));
				actionForm.setCustomers_remark(CommonTools.null2String(rest
						.getString("customers_remark")));
				actionForm.setGroups(groups);
				actionForm.setStockpile(stockpile);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}
	}
	/**
	 * @新增保存
	 * @要记录操作，在插入后要向操作记录表也插一条语句，也就是用执行多天SQL， @ 所有用super.executeUpdate(sqlList,true)执行
	 */
	public boolean SAVE_INSERT(F010104ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) {
		boolean falg = false;
		List sqlList = new ArrayList();
		/** 存放SQL的LIST */
		StringBuffer yhczsql = new StringBuffer();
		/** 操作记录SQL */
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		/** 获得操作记录SQL */
		StringBuffer msql = new StringBuffer();
		msql.append("INSERT INTO pd_consignment(");
		msql.append("  manufacturing_order,");
		msql.append("  groups,");
		msql.append("  stockpile,");
		msql.append("  take_over,");
		msql.append("  contact_telephone,");
		msql.append("  send_locus,");
		msql.append("  send_amount,");
		msql.append("  send_date,");
		msql.append("  remark");
		msql.append(") VALUES (");
		msql.append(" '"+actionForm.getManufacturing_order()+"',");
		msql.append(" '"+actionForm.getGroups()+"',");
		msql.append(" '"+actionForm.getStockpile()+"',");
		msql.append(" '"+actionForm.getTake_over()+"',");
		msql.append(" '"+actionForm.getContact_telephone()+"',");
		msql.append(" '"+actionForm.getSend_locus()+"',");
		msql.append(" '"+actionForm.getSend_amount()+"',");
		msql.append(" '"+actionForm.getSend_date()+"',");
		msql.append(" '"+actionForm.getRemark()+"'");
		msql.append(")");

		try {
			log.info("发货信息管理 SAVE_INSERT sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			falg = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
			/** 成功 */
		} catch (SQLException ex) {
			if ("1".equals(String.valueOf(ex.getErrorCode()))) {
				/** ex.getErrorCode()返回一，表示主键冲突 */
				actionForm.setErrorFlag("1");
				/** 主键冲突! */
			} else {
				actionForm.setErrorFlag("1");
				/** 操作失败 */
			}
		} catch (Exception e) {
			actionForm.setErrorFlag("3");
			/**  */
			e.printStackTrace();
			super.rollback();

		}
		super.commit();

		return falg;
	}

}
